package com.vitechinc.v3.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;

/**
 * Encapsulates data access to a simple set of tables, PERSON and PERSON_TYPE
 * PERSON_TYPE
   create table person_type 
       (person_type_id int not null, 
        internal_name varchar(50) not null, 
        display_name varchar(50) not null,
        constraint ak_person_type unique (internal_name), 
        constraint pk_person_type primary key (person_type_id))
 * PERSON
   create table person (
       person_id int not null, 
       person_type_id int not null, 
       name varchar(50) not null, 
       ssn varchar(9) null, 
       constraint pk_person primary key (person_id), 
       constraint fk_person_type foreign key (person_type_id) references person_type (person_type_id))
 * Thus each person has a type, a name, and an optional ssn of at most 9 characters.
 * @author BBinford
 *
 */
public class PersonDAO {
	DataSource dSource;
	Connection connection;
	public PersonDAO(DataSource dataSource)
	{
		
			dSource = dataSource;
		
	}
	
	/**
	 * Inserts a new record into the person table
	 * @param personId the id of the person to insert
	 * @param personName the name of the person to insert
	 * @param personType the internal name of the person_type to associate with this person record
	 * @throws ValidationException when a required field is null or doesn't meet the database constraints
	 * @throws SQLException on database error
	 */
	public void insert(int personId, String personName, String personType) throws ValidationException, SQLException
	{
		PreparedStatement statement =null;
		try{
		connection = dSource.getConnection();
		if(personId == 0 || personName == null || personType == null)
			throw new ValidationException();
		
		statement = connection.prepareStatement("select person_type_id from person_type where internal_name =?");
		statement.setString(1, personType);		
		ResultSet resultSet = statement.executeQuery();
		if(!resultSet.next())
		{
			throw new ValidationException();
		}
		 statement = connection.prepareStatement("INSERT INTO PERSON (PERSON_ID,person_type_id,NAME)" +
				" VALUES (?,select person_type_id from person_type where internal_name =? ,?)");
		
			statement.setInt(1, personId);
			statement.setString(2, personType);
			statement.setString(3, personName);			
			statement.executeUpdate();
			connection.commit();					
		}
		catch(SQLException sqlException){
			if (connection != null) {
		        try {
		          System.err.print("Transaction is being rolled back");
		          connection.rollback();
		          connection.close();
		          if(statement !=null)
			          statement.close();
		        } catch(SQLException excep) {
		          excep.printStackTrace();
		        }
			}
		}
		finally{
			try{
				if (connection != null) 
					 connection.close();
				if(statement !=null)
					statement.close();				
			}catch(SQLException exception)			{
				exception.printStackTrace();
			}
		}
		
	}
	
	/**
	 * Inserts a new record into the person table
	 * @param personId the id of the person to insert
	 * @param personName the name of the person to insert
	 * @param personType the internal name of the person_type to associate with this person record
	 * @param ssn the ssn to set on the person record
	 * @throws ValidationException when a required field is null or doesn't meet the database constraints
	 * @throws SQLException on database error
	 */
	public void insert(int personId, String personName, String personType, String Ssn) throws ValidationException, SQLException
	{
		connection = dSource.getConnection();
		PreparedStatement statement = null;
		try{
		if(personId == 0 || personName == null || personType == null)
			throw new ValidationException();
		if(Ssn!=null && Ssn.length()>9)
			throw new ValidationException();
		statement = connection.prepareStatement("select person_type_id from person_type where internal_name =?");
		statement.setString(1, personType);		
		ResultSet resultSet = statement.executeQuery();
		if(resultSet ==null)
			throw new ValidationException();
		
		statement = connection.prepareStatement("INSERT INTO PERSON (PERSON_ID,person_type_id,NAME,SSN)" +
		" VALUES (?,select person_type_id from person_type where internal_name =? ,?,?)");
		
			statement.setInt(1, personId);
			statement.setString(2, personType);
			statement.setString(3, personName);			
			statement.setString(4, Ssn);	
			statement.executeUpdate();
			connection.commit();					
		}
		catch(SQLException sqlException)		{
			if (connection != null) {
		        try {
		          System.err.print("Transaction is being rolled back");
		          connection.rollback();
		          connection.close();
		          if(statement !=null)
		          statement.close();
		        } catch(SQLException excep) {
		          excep.printStackTrace();
		        }
			}
		}
		finally{
			try{
				if (connection != null) 
					 connection.close();
				if(statement !=null)
					statement.close();	
			}catch(SQLException exception)			{
				exception.printStackTrace();
			}
		}
		

	}

	/**
	 * Retrieves a list of person names from the database that are related to the specified person type
	 * @param personType the internal name of the person type to search for
	 * @return a list of strings, each item in the list being the name field from a person record
	 */
	public List<String> findPersonNamesOfType(String personType) throws ValidationException, SQLException
	{
		connection = dSource.getConnection();
		PreparedStatement statement = null;
		List<String> names = new ArrayList<String>();
		try{
		
		statement = connection.prepareStatement("SELECT name FROM person,person_type where person.person_type_id = person_type.person_type_id and " +
				"person_type.internal_name = ?");
		statement.setString(1, personType);
		
			ResultSet results = statement.executeQuery();			
	         while (results.next()) {
	             names.add(results.getString(1));
	         }
		}catch(SQLException sqlException)		{
			if (connection != null) {
				 
		        try {
		        	connection.close();
		        	if (connection != null)
		        		statement.close();
		        } catch(SQLException excep) {
		          excep.printStackTrace();
		        }
			}
		}
		finally{
			try{
				if (connection != null) 
					 connection.close();
				if(statement !=null)
					statement.close();			
			}catch(SQLException exception)			{
				exception.printStackTrace();
			}
		}
		 return names;
		
	}
}
